#!/usr/bin/python
# -*- coding: utf-8 -*-

""" 
-------------------------------------------------
@version    : v1.0 
@author     : fangzheng
@contact    : zfang@hillinsight.com
@software   : PyCharm 
@filename   : oraclehelper.py
@create time: 2019/3/26 22:03 
@describe   : 
@use example: python oraclehelper.py [param1 param2]
-------------------------------------------------
"""

from IDBHelper import DBHelper
from logger import Logger
from dbutils import select,execute,DBConfig
import cx_Oracle
import traceback

log = Logger(name='oraclehelper')


class OracleHelper(DBHelper):
    """
    OracleHelper
    """
    _connect = None

    def __init__(self, db_config):
        self.init(db_config)

    def init(self, db_config):
        """
        init database connection
        :param dbconfig:
        :return: True/False
        """
        try:
            dbconfig = DBConfig(db_config)
            connstr = '{user}/"{pa}"@{host}:{port}/{db}'.format(user=dbconfig.username,
                                                                pa=dbconfig.password,
                                                                host=dbconfig.host,
                                                                port=dbconfig.port,
                                                                db=dbconfig.db)
            self._connect = cx_Oracle.connect(connstr)
            log.info(" Connected to Oracle database [ {db} ]...".format(db=dbconfig.db))
            return True
        except Exception as e:
            log.error(" Connect Oracle exception : \n{e}\n".format(e=str(e)))
            return False

    def get_conn(self):
        if self._connect:
            return self._connect
        else:
            self.init()
            return self._connect

    def close_conn(self):
        if self._connect:
            self._connect.close()
            log.info(" Oracle database connection closed....")

    def table_is_exist(self,table_name):
        """
        Check table is exist
        :param tablename:
        :return:
        """
        sql = "select count(*) from user_tables where table_name = upper('{tname}')".format(tname=table_name)
        rows = self.select(sql=sql)
        cnt = 0
        if len(rows):
            cnt = rows.pop()[0]

        if cnt >= 1:
            return True
        else:
            return False

    def select(self, sql, param=None , size=None):
        """
        Query data
        :param sql:
        :param param:
        :param size: Number of rows of data you want to return
        :return:
        """
        # return select(self._connect,sql,param,size)

        cur = self._connect.cursor()
        rows = None
        try:
            cur.execute(sql)
            if size:
                rows = cur.fetchmany(size)
            else:
                rows = cur.fetchall()
        except Exception as e:
            self._connect.rollback()
            log.error(traceback.format_exc())
            log.error("[sql]:{} [param]:{}".format(sql, param))
        cur.close()
        return rows

    def execute(self,sql, param=None):
        """
        exec DML：INSERT、UPDATE、DELETE
        :param sql: dml sql
        :param param: string|list
        :return: Number of rows affected
        """
        return execute(self._connect, sql, param)